package com.gt.controller.ipToAdd;


import ch.qos.logback.core.net.SyslogOutputStream;
import com.alibaba.fastjson.JSONObject;
import com.gt.utils.JDBCUtil;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import sun.applet.Main;

import java.io.IOException;
import java.net.InetSocketAddress;
import java.net.Proxy;
import java.net.SocketAddress;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * ip转换为地址
 */
@RestController
public class IpToAddController {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;



    @GetMapping("sys/addBq")
    public String addBq(int num){
        Connection conn1 = JDBCUtil.getConnection4Mysql(url,username, password);
        String result = null;
        List<String> _iplist = new ArrayList<>();
        List<Integer> _portlist = new ArrayList<>();
        String url = "https://www.kuaidaili.com/ops/proxylist/PAGE/";
        for (int i = 1; i <= 10; i++) {
            url = url.replace("PAGE", i + "");
            Document d = null;
            try {
                d = Jsoup.connect(url).get();
            } catch (IOException e) {
                e.printStackTrace();
            }
            Element div = d.getElementById("freelist");
            Element tbody = div.getElementsByTag("tbody").get(0);
            Elements tr = tbody.getElementsByTag("tr");
            for (Element item : tr) {
                _iplist.add(item.getElementsByTag("td").get(0).text());
                _portlist.add(Integer.valueOf(item.getElementsByTag("td").get(1).text()));
            }
        }
        int size = _iplist.size() > _portlist.size() ? _portlist.size() : _iplist.size();

        String sql = "select g.id,g.ip,g.city from gt_project_jsdebug_details g where g.region is null order by g.id desc";
        ResultSet rs = null;
        try {
            rs = conn1.createStatement().executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        int all = 0;
        try {
            all = rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        int fail = 0;
        StringBuilder sb = new StringBuilder("insert into gt_project_jsdebug_details(id,country,region,city,country_id,region_id,city_id) values ");
        int flag = 0;
        try {
            while (rs.next()) {
                flag ++;
                try {
    //                String edit = "update gt_project_jsdebug_details set country=COUNTRY1,region=REGION1,city=CITY1,country_id=COUNTRYID,region_id=REGIONID,city_id=CITYID where id=GID";
                    int random = new Random().nextInt(size);
                    SocketAddress addr = new InetSocketAddress(_iplist.get(random), _portlist.get(random));
                    Proxy proxy = new Proxy(Proxy.Type.HTTP, addr);
                    JSONObject json = JSONObject.parseObject(Jsoup.connect("http://ip.taobao.com/service/getIpInfo.php?ip=" + rs.getString(2)).get().text());

                    if (!"".equals(json.getString("data"))) {
                        JSONObject item = JSONObject.parseObject(json.getString("data"));
                        sb.append("(");
                        sb.append(rs.getString(1)).append(",")
                                .append("'" + item.getString("country") + "'").append(",")
                                .append("'" + item.getString("region") + "'").append(",")
                                .append("'" + item.getString("city") + "'").append(",")
                                .append("'" + item.getString("country_id") + "'").append(",")
                                .append("'" + item.getString("region_id") + "'").append(",")
                                .append("'" + item.getString("city_id") + "'");
                        sb.append("),");
    //                    edit = edit.replace("COUNTRY1", "'" + item.getString("country") + "'")
    //                            .replace("REGION1", "'" + item.getString("region") + "'")
    //                            .replace("CITY1", "'" + item.getString("city") + "'")
    //                            .replace("COUNTRYID", "'" + item.getString("country_id") + "'")
    //                            .replace("REGIONID", "'" + item.getString("region_id") + "'")
    //                            .replace("CITYID", "'" + item.getString("city_id") + "'")
    //                            .replace("GID", rs.getString(1));
                        if(flag == num){
                            StringBuilder sb_edit = new StringBuilder();
                            sb_edit.append(sb.substring(0, sb.length()-1));
                            sb_edit.append(" on duplicate key update country=values(country),region=values(region),city=values(city),country_id=values(country_id),region_id=values(region_id),city_id=values(city_id)");
                            final String edit_sql = sb_edit.toString();
                            System.out.println(edit_sql);
                            conn1.createStatement().executeUpdate(edit_sql);
                            sb = new StringBuilder("insert into gt_project_jsdebug_details(id,country,region,city,country_id,region_id,city_id) values ");
                            flag = 0;
    //                        new Thread(() ->{
    //                            try {
    //                                System.out.println(edit_sql);
    //                            } catch (SQLException e) {
    //                                e.printStackTrace();
    //                            }
    //                        }).start();
                        }
                    } else {
                        fail++;
                    }
                }catch (Exception ex){
                    ex.printStackTrace();
                    System.out.println(ex.getMessage());
                    fail++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "总记录数="+all+"失败记录数="+fail+"成功记录数="+(all-fail);
    }

    public static void main(String[] args) {
        StringBuilder sb = new StringBuilder("123");
        sb.append("555");
        System.out.println(sb);
        sb = new StringBuilder("123");
        System.out.println(sb);
    }
}
